﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using iAnywhere.Data.SQLAnywhere;
using Oracle.DataAccess.Client;
using System.Configuration;
using Syncer;
using System.Globalization;
using System.Xml;
using System.Xml.Serialization;
using System.IO;

namespace SybaseTest
{
    public class Nd_Performance_Collector
    {

        public static void Main(string[] args)
        {
            try
            {
                Log.OpenLog();
                long fromtime = 0;
                long totime = 0;
                try
                {
                    //获取格林尼治时间
                    gmtTimeEntity gte = GetGMTDateTime();
                    fromtime = gte.fromtime;
                    totime = gte.totime;
                }
                catch (Exception ex)
                {
                    Log.Exception("", ex);
                    return;
                }
                try
                {
                    var path = "E:\\Program\\t_Performance\\sql.xml";
                    //读取 xml文件
                    String xmlsql = File.ReadAllText(path);
                    root objroot = XmlDeserialize<root>(xmlsql);

                    //获取宁德地区的cleardevice的sql语句
                    String nd_device_Clr_Str = objroot.ningde.device.delete;
                    //获取宁德地区的loaddevice的sql语句
                    String nd_device_load_Str = objroot.ningde.device.load;
                    //获取宁德地区的loadperformance语句数组
                    String[] nd_performance_load_Arr = objroot.ningde.performance.load;
                    //获取宁德地区的loadtmartavai的sql语句
                    String nd_tmartavai_load_Str = objroot.ningde.tmartavai.load;

                    //获取三明地区的cleardevice的sql语句
                    String sm_device_Clr_Str = objroot.sanming.device.delete;
                    //获取三明地区的loaddevice的sql语句
                    String sm_device_load_Str = objroot.sanming.device.load;
                    //获取三明地区的loadperformance语句数组
                    String[] sm_performance_load_Arr = objroot.sanming.performance.load;
                    //获取三明地区的loadtmartavai的sql语句
                    String sm_tmartavai_load_Str = objroot.sanming.tmartavai.load;

                    //获取福建省局的cleardevice的sql语句
                    String sheng_device_Clr_Str = objroot.sheng.device.delete;
                    //获取福建省局的loaddevice的sql语句
                    String sheng_device_load_Str = objroot.sheng.device.load;
                    //获取福建省局的loadperformance语句数组
                    String[] sheng_performance_load_Arr = objroot.sheng.performance.load;
                    //获取福建省局的loadtmartavai的sql语句
                    String sheng_tmartavai_load_Str = objroot.sheng.tmartavai.load;

                    string bppmConn = string.Empty;
                    foreach (var bppmConnKey in ConfigurationManager.AppSettings.Keys)
                    {
                        if (bppmConnKey.ToString() == "宁德市烟草专卖局")
                        {
                            bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];

                            try
                            {
                                ClearDeviceTable(bppmConn, nd_device_Clr_Str);
                                LoadDeviceTable(bppmConn, nd_device_load_Str);
                                LoadPerformanceTable2(fromtime, totime, bppmConn, nd_performance_load_Arr);
                                LoadTmartAvailability(fromtime, totime, bppmConn, nd_tmartavai_load_Str);
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("", ex);
                                continue;
                            }
                        }
                        else if (bppmConnKey.ToString() == "福建省烟草专卖局")
                        {
                            bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];

                            try
                            {
                                ClearDeviceTable(bppmConn, sheng_device_Clr_Str);
                                LoadDeviceTable(bppmConn, sheng_device_load_Str);
                                LoadPerformanceTable2(fromtime, totime, bppmConn, sheng_performance_load_Arr);
                                LoadTmartAvailability(fromtime, totime, bppmConn, sheng_tmartavai_load_Str);
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("", ex);
                                continue;
                            }
                        }
                        else if (bppmConnKey.ToString() == "三明市烟草专卖局")
                        {
                            bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];

                            try
                            {
                                ClearDeviceTable(bppmConn, sm_device_Clr_Str);
                                LoadDeviceTable(bppmConn, sm_device_load_Str);
                                LoadPerformanceTable2(fromtime, totime, bppmConn, sm_performance_load_Arr);
                                LoadTmartAvailability(fromtime, totime, bppmConn, sm_tmartavai_load_Str);
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("", ex);
                                continue;
                            }
                        }

                        //if (string.IsNullOrEmpty(bppmConn)) //skip empty value
                        //{
                        //     Console.WriteLine("The value of " + bppmConnKey + " is empty, skip it.");
                        //     continue;
                        // }
                    }
                }
                catch (Exception ex)
                {
                    Log.Exception("", ex);
                    return;
                }
            }
            finally { Log.CloseLog(); }
        }

        public static gmtTimeEntity GetGMTDateTime()
        {
            Log.Info("begin to get GMT time ...................");
            DateTime lastDate = DateTime.Now.AddDays(-1);
            DateTime currentDate = DateTime.Now;

            //DateTime lastDate = DateTime.Now.AddDays(-2);
            //DateTime currentDate = DateTime.Now.AddDays(-1);

            String StrLastDate = lastDate.ToShortDateString().ToString();
            String StrCurrentDate = currentDate.ToShortDateString().ToString();

            String StrTimeFrom = StrLastDate + " 00:00:00";
            String StrTimeTo = StrCurrentDate + " 00:00:00";

            DateTime DtTimeFrom = DateTime.Parse(StrTimeFrom);
            DateTime DtTimeTo = DateTime.Parse(StrTimeTo);

            Log.Info("start time for calc is " + DtTimeFrom.ToString());
            Log.Info("end time for calc is " + DtTimeTo.ToString());

            long fromTime = (DtTimeFrom.Ticks - 621355968000000000) / 10000000 - 28800;
            long toTime = (DtTimeTo.Ticks - 621355968000000000) / 10000000 - 28800;

            Log.Info("GMT Start Time is " + fromTime.ToString());
            Log.Info("GMT End Time is " + toTime.ToString());

            return new gmtTimeEntity
            {
                fromtime = fromTime,
                totime = toTime

            };



        }

        public static void ClearDeviceTable(string bppmStr, string sql)
        {
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                Log.Info("RUN CLEAR DEVICE TABLE SQL:" + sql);

                SACommand cmd = new SACommand(sql, conn);
                int rd = cmd.ExecuteNonQuery();
                conn.Close();
            }

        }

        public static void DropPerformanceTable(string bppmStr, string sql)
        {
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                Log.Info("=====RUN DROP PERFORMANCE TABLE SQL:" + sql);

                //删除t_device表
                SACommand cmd = new SACommand(sql, conn);
                SADataReader rd = cmd.ExecuteReader();
                conn.Close();
                return;
            }

        }

        public static void DropTmartAviTable(string bppmStr)
        {
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                //删除t_device表
                string sql = string.Empty;

                sql = @"drop table t_tmartAvailability";

                SACommand cmd = new SACommand(sql, conn);
                int result = cmd.ExecuteNonQuery();

                Log.Info("=====RUN DROP TMARTAVAI TABLE SQL:" + sql);
                conn.Close();
                return;

            }

        }

        static void CreatePerformaceTable(string bppmStr, string sql)
        {
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                Log.Info("=====RUN CREATE PERFORMANCE TABLE SQL:" + sql);

                //新建t_device表
                SACommand cmd = new SACommand(sql, conn);
                SADataReader rd = cmd.ExecuteReader();

                rd.Close();
            }
        }

        public static void CreateDeviceTable(string bppmStr, string sql)
        {

            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                Log.Info("=====RUN CREATE DEVICE TABLE SQL:" + sql);

                SACommand cmd = new SACommand(sql, conn);
                SADataReader rd = cmd.ExecuteReader();

                rd.Close();
                conn.Close();
            }
        }

        public static void CreateTmartAviTable(string bppmStr, string sql)
        {

            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                Log.Info("=====RUN CREATE TMARTAVAI TABLE SQL:" + sql);

                SACommand cmd = new SACommand(sql, conn);
                int result = cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        public static void LoadDeviceTable(string bppmStr, string sql)
        {
            try
            {
                using (SAConnection conn = new SAConnection(bppmStr))
                {
                    conn.Open();

                    SACommand cmd = new SACommand(sql, conn);
                    int rd = cmd.ExecuteNonQuery();

                    Log.Info("=====RUN LOAD DEVICE TABLE SQL:" + sql);
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Exception("", ex);
            }
        }

        public static void LoadPerformanceTable(long fromTime, long toTime, string bppmStr)
        {
            int n = 0;

            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                List<ndPerformanceEntity> ndpe = new List<ndPerformanceEntity>();

                string sql = string.Empty;


                ////如果已有前一天数据则跳出
                sql = @"select max(FromTime) from t_performance with(NOLOCK)";
                SACommand cmd = new SACommand(sql, conn);
                SADataReader rd = cmd.ExecuteReader();
                String earlyTimeStr = string.Empty;
                long earlyTimeLong = 0;
                while (rd.Read())
                {
                    if (rd.FieldCount == 0) return;
                    else
                        earlyTimeStr = rd.GetValue(0).ToString();
                    if (earlyTimeStr == "") earlyTimeStr = "0";
                    else continue;

                }
                earlyTimeLong = Int64.Parse(earlyTimeStr);

                if (earlyTimeLong >= fromTime)
                    return;

                ///计算ID
                sql = @"select max(ID) from t_performance with(NOLOCK)";
                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();

                while (rd.Read())
                {
                    if (rd[0] is DBNull) n = 0;
                    else
                        n = Convert.ToInt32(rd[0]);
                }

                /////开始从_PATROL__UNIX_OS_MEMORY_RT_VIEW中获取performance数据

                sql = @"select a.moinstid, b.devicename,'MEMFREEMEM' Parameter,'Unix 剩余内存' ParameterDisplay, round(avg(a.MEMFREEMEM_AVG),3) as value,getdate() as CreateTime, "
                 + fromTime
                 + " as fromtime,"
                 + toTime
                 + " as totime from dba._PATROL__UNIX_OS_MEMORY_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                         + fromTime
                         + " and a.totime <="
                         + toTime
                         + " and a.moinstid=b.MOINSTID"
                         + " and MEMFREEMEM_AVG!=-2.147483648E7"
                         + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__UNIX_OS_MEMORY_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }

                /////开始从_PATROL__UNIX_OS_STORAGE_FILESYSTEM_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'FSCAPACITY' Parameter,'Unix 文件系统使用率' ParameterDisplay, round(avg(a.FSCAPACITY_AVG),3) as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__UNIX_OS_STORAGE_FILESYSTEM_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and FSCAPACITY_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__UNIX_OS_STORAGE_FILESYSTEM_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }
                /////开始从_PATROL__UNIX_OS_CPU_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'CPUCPUUTIL' Parameter,'Unix CPU使用率' ParameterDisplay, round(avg(a.cpucpuutil_AVG),3) as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__UNIX_OS_CPU_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and cpucpuutil_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__UNIX_OS_CPU_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }

                /////开始从_PATROL__UNIX_OS_STORAGE_SWAP_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'SWPTOTSWAPUSEDPERCENT' Parameter,'SWAP空间利用率' ParameterDisplay, round(avg(a.SWPTOTSWAPUSEDPERCENT_AVG),3) as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__UNIX_OS_STORAGE_SWAP_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and SWPTOTSWAPUSEDPERCENT_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__UNIX_OS_STORAGE_SWAP_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }

                /////开始从_PATROL__ORACLE_ORACLE_INSTANCE_ORACLE_AVAILABILITY_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'CONNECTDB' Parameter,'oracle数据库连接状态(正常百分比%)' ParameterDisplay, (1-round(sum(CONNECTDB_AVG)/count(*),3))*100 as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__ORACLE_ORACLE_INSTANCE_ORACLE_AVAILABILITY_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and CONNECTDB_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__ORACLE_ORACLE_INSTANCE_ORACLE_AVAILABILITY_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }

                /////开始从_PATROL__ORACLE_ORACLE_INSTANCE_ORACLE_AVAILABILITY_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'INSTANCESTATUS' Parameter,'oracle实例状态(正常百分比%)' ParameterDisplay, (1-round(sum(INSTANCESTATUS_AVG)/count(*),3))*100 as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__ORACLE_ORACLE_INSTANCE_ORACLE_AVAILABILITY_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and INSTANCESTATUS_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__ORACLE_ORACLE_INSTANCE_ORACLE_AVAILABILITY_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }

                ////开始从_PATROL__ORANET_ORANET_LISTENER_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'LISTENERSTATUS' Parameter,'oracle数据库监听状态(正常百分比%)' ParameterDisplay, (1-round(sum(LISTENERSTATUS_AVG)/count(*),3))*100 as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__ORANET_ORANET_LISTENER_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and LISTENERSTATUS_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__ORANET_ORANET_LISTENER_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }


                ////开始从_PATROL__ORANET_ORANET_LISTENER_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'APPSERVERAVAILABILITY' Parameter,'was中间件连接状态(正常百分比%)' ParameterDisplay, (1-round(sum(APPSERVERAVAILABILITY_AVG)/count(*),3))*100 as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__PMW_MAIN_PMW_NODE_PMW_APPSERVER_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and APPSERVERAVAILABILITY_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__PMW_MAIN_PMW_NODE_PMW_APPSERVER_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }


                ////y-开始从_PATROL__NT_OS_NT_CPU_CONTAINER_NT_CPU_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'CPUCPUUTIL' Parameter,'Windows CPU使用率' ParameterDisplay, round(avg(CPUPRCRPROCESSORTIMEPERCENT_AVG),3) as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__NT_OS_NT_CPU_CONTAINER_NT_CPU_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and CPUPRCRPROCESSORTIMEPERCENT_AVG!=-2.147483648E7  and b.instname='_Total'"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__NT_OS_NT_CPU_CONTAINER_NT_CPU_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }

                ////y-开始从_PATROL__NT_OS_NT_MEMORY_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'MEMFREEMEM' Parameter,'Windows 剩余内存' ParameterDisplay, round(avg(MEMMEMAVAILABLEBYTES_AVG),3) as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__NT_OS_NT_MEMORY_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and MEMMEMAVAILABLEBYTES_AVG!=-2.147483648E7"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__NT_OS_NT_MEMORY_RT_VIEW");

                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }

                ////y-开始从_PATROL__NT_OS_NT_LOGICAL_DISKS_CONTAINER_NT_LOGICAL_DISKS_RT_VIEW中获取performance数据

                sql = string.Empty;

                sql = @"select a.moinstid, b.devicename,'FSCAPACITY' Parameter,'Windows 系统磁盘使用率' ParameterDisplay, round(avg(LDLDDISKTIMEPERCENT_AVG),3) as value,getdate() as CreateTime, "
                + fromTime
                + " as fromtime,"
                + toTime
                + " as totime from dba._PATROL__NT_OS_NT_LOGICAL_DISKS_CONTAINER_NT_LOGICAL_DISKS_RT_VIEW a,t_device b with(NOLOCK) where a.fromtime>="
                        + fromTime
                        + " and a.totime <="
                        + toTime
                        + " and a.moinstid=b.MOINSTID"
                        + " and LDLDDISKTIMEPERCENT_AVG!=-2.147483648E7 and b.instname='Logical Disk (C:)'"
                        + " group by a.moinstid,b.devicename";

                Log.Info("Now is loading table _PATROL__NT_OS_NT_LOGICAL_DISKS_CONTAINER_NT_LOGICAL_DISKS_RT_VIEW");



                //////////////////
                cmd = new SACommand(sql, conn);
                rd = cmd.ExecuteReader();


                while (rd.Read())
                {
                    ndpe.Add(new ndPerformanceEntity
                    {
                        Moinstid = rd["moinstid"].ToString(),
                        Identify = rd["devicename"].ToString(),
                        Parameter = rd["Parameter"].ToString(),
                        ParameterDisplay = rd["ParameterDisplay"].ToString(),
                        value = rd["value"].ToString(),
                        CreateTime = (DateTime)rd["CreateTime"],
                        FromTime = rd["fromtime"].ToString(),
                        ToTime = rd["totime"].ToString(),
                    });
                }


                ///将list中的数据插入到t_performance中

                foreach (var performance in ndpe)
                {
                    try
                    {
                        String moinstidStr = performance.Moinstid;
                        String ipStr = performance.Identify;
                        String ParameterStr = performance.Parameter;
                        String ParameterDisplayStr = performance.ParameterDisplay;
                        String valueStr = performance.value;
                        DateTime createtimeDt = performance.CreateTime;
                        String fromtimeStr = performance.FromTime;
                        String totimeStr = performance.ToTime;

                        sql = "insert into t_performance values ("
                            + n++ + ", '"
                            + moinstidStr + "', '"
                            + ipStr + "', '"
                            + ParameterStr + "', '"
                            + ParameterDisplayStr + "', '"
                            + valueStr + "', "
                            + "getdate(), "
                            + fromtimeStr + ", "
                            + totimeStr + ")";

                        cmd = new SACommand(sql, conn);
                        int i = cmd.ExecuteNonQuery();

                        Log.Info("One performace Inserted.");


                    }
                    catch (Exception ex)
                    {
                        Log.Exception("", ex);
                    }

                }
                rd.Close();
                conn.Close();
            }

            Log.Info("end to test...");
        }

        public static void LoadPerformanceTable2(long fromTime, long toTime, string bppmStr, String[] sqlArr)
        {
            int n = 0;

            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                List<ndPerformanceEntity> ndpe = new List<ndPerformanceEntity>();

                string max_sql = string.Empty;


                ////如果已有前一天数据则跳出
                max_sql = @"select max(FromTime) from t_performance with(NOLOCK)";
                SACommand cmd = new SACommand(max_sql, conn);
                SADataReader rd = cmd.ExecuteReader();
                String earlyTimeStr = string.Empty;
                long earlyTimeLong = 0;
                while (rd.Read())
                {
                    if (rd.FieldCount == 0) return;
                    else
                        earlyTimeStr = rd.GetValue(0).ToString();
                    if (earlyTimeStr == "") earlyTimeStr = "0";
                    else continue;

                }
                earlyTimeLong = Int64.Parse(earlyTimeStr);

                if (earlyTimeLong >= fromTime)
                    return;

                ///计算ID
                String id_sql = @"select max(ID) from t_performance with(NOLOCK)";
                cmd = new SACommand(id_sql, conn);
                rd = cmd.ExecuteReader();

                while (rd.Read())
                {
                    if (rd[0] is DBNull) n = 0;
                    else
                        n = Convert.ToInt32(rd[0]);
                }

                foreach (var sql in sqlArr)
                {
                    String sqlStr = FilterSQL(sql, fromTime, toTime);
                    cmd = new SACommand(sqlStr, conn);
                    rd = cmd.ExecuteReader();


                    while (rd.Read())
                    {
                        ndpe.Add(new ndPerformanceEntity
                        {
                            Moinstid = rd["moinstid"].ToString(),
                            Identify = rd["devicename"].ToString(),
                            Parameter = rd["Parameter"].ToString(),
                            ParameterDisplay = rd["ParameterDisplay"].ToString(),
                            value = rd["value"].ToString(),
                            CreateTime = (DateTime)rd["CreateTime"],
                            FromTime = rd["fromtime"].ToString(),
                            ToTime = rd["totime"].ToString(),
                        });
                    }
                }


                ///将list中的数据插入到t_performance中

                foreach (var performance in ndpe)
                {
                    try
                    {
                        String moinstidStr = performance.Moinstid;
                        String ipStr = performance.Identify;
                        String ParameterStr = performance.Parameter;
                        String ParameterDisplayStr = performance.ParameterDisplay;
                        String valueStr = performance.value;
                        DateTime createtimeDt = performance.CreateTime;
                        String fromtimeStr = performance.FromTime;
                        String totimeStr = performance.ToTime;

                        String inssql = "insert into t_performance values ("
                            + n++ + ", '"
                            + moinstidStr + "', '"
                            + ipStr + "', '"
                            + ParameterStr + "', '"
                            + ParameterDisplayStr + "', '"
                            + valueStr + "', "
                            + "getdate(), "
                            + fromtimeStr + ", "
                            + totimeStr + ")";

                        cmd = new SACommand(inssql, conn);
                        int i = cmd.ExecuteNonQuery();

                        Log.Info("=====Run INSERT PERFORMANCE SQL:" + inssql);

                    }
                    catch (Exception ex)
                    {
                        Log.Exception("", ex);
                        continue;
                    }

                }
                rd.Close();
                conn.Close();
            }

        }

       public  static void LoadTmartAvailability(long fromTime, long toTime, string bppmStr, string sql)
        {

            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                List<ndTmartAvaiEntity> ndpe = new List<ndTmartAvaiEntity>();

                //如果已有前一天数据则跳出

                string maxsql = string.Empty;
                maxsql = @"select max(FromTime) from t_TmartAvailability with(NOLOCK)";

                SACommand cmd = new SACommand(maxsql, conn);
                SADataReader rd = cmd.ExecuteReader();
                String earlyTimeStr = string.Empty;
                long earlyTimeLong = 0;
                while (rd.Read())
                {
                    if (rd.FieldCount == 0) return;
                    else
                        earlyTimeStr = rd.GetValue(0).ToString();
                    if (earlyTimeStr == "") earlyTimeStr = "0";
                    else continue;

                }
                earlyTimeLong = Int64.Parse(earlyTimeStr);

                if (earlyTimeLong >= fromTime)
                    return;

                /////开始从dba.TMARTTRANSACTIONS_CFG_VIEW中和dba.TMARTTRANSACTIONS_RT_VIEW获取performance数据

                String sqlStr = FilterSQL(sql, fromTime, toTime);
                cmd = new SACommand(sqlStr, conn);
                int result = cmd.ExecuteNonQuery();

                Log.Info("=====Run LOAD TMARTAVAI SQL:" + sql);
                conn.Close();
            }
        }

        private static T XmlDeserialize<T>(string str) where T : class
        {
            XmlSerializer mySerializer = new XmlSerializer(typeof(T));
            StreamReader mem2 = new StreamReader(
                    new MemoryStream(System.Text.Encoding.Unicode.GetBytes(str)),
                    System.Text.Encoding.Unicode);

            return (T)mySerializer.Deserialize(mem2);
        }

        public static string FilterSQL(string strVAlue, long fromtime, long totime)
        {
            string str = "";
            str = strVAlue.Replace(":fromTime", fromtime.ToString());
            str = str.Replace(":toTime", totime.ToString());
            return str;
        }

    }


}
